package Dao;

import JavaBean.Book;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 刘俊杰
 * @create 2021-04-27 21:23
 */
public class userBooksDao {

    public int insert(int userID, String bookID) {
        System.out.println("userID = " + userID);
        System.out.println("bookID = " + bookID);
        Connection conn = null;
        PreparedStatement pstmt = null;
        PreparedStatement pstmt1 = null;
        ResultSet rs = null;
        ResultSet rs1 = null;
        int i  = 0;
        try {
            conn = DBUtils.getConn();
            pstmt = conn.prepareStatement("select * from userbooks where id= '" + bookID + "'");
            pstmt1 = conn.prepareStatement("select * from userbooks where userID= '" + userID + "'");
            rs = pstmt.executeQuery();
            rs1 = pstmt1.executeQuery();
            System.out.println(pstmt);
            System.out.println(pstmt1);
            System.out.println(rs);
            System.out.println(rs.next());
            System.out.println(rs1.next());
            if(rs.next()&&rs1.next()){
                System.out.println("添加书架失败，书本已存在,请勿重复添加");
                i = 0;
            }else{
                //插入从前端页面传来的userID,和bookID,并写入数据库保存
                String sql = "Insert into userbooks(userID,id) values(?,?)";
                pstmt = conn.prepareStatement(sql);
                pstmt.setInt(1,userID);
                pstmt.setString(2,bookID);

                i = pstmt.executeUpdate();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.close(rs,pstmt,conn);
        }
        return  i;
    }

    /**&
     *
     * @param userID 通过userID查到该用户拥有的所有书籍bookID,然后再通过bookID查询到书籍的内容
     * @return
     */
    public List<Book> getUserAllBooksByUserID(int userID){
        List<Book> list = new ArrayList<Book>();
        Book books = null;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = DBUtils.getConn();
            pstmt = conn.prepareStatement("SELECT books.id,books.bookname,books.author,books.isbn,books.publish,books.bookresume,books.publishdate FROM userbooks INNER JOIN books ON userbooks.id = books.id  WHERE userbooks.userID = ?;");
            pstmt.setInt(1,userID);
            rs = pstmt.executeQuery();
            // 展开结果集数据库
            while (rs != null && rs.next()) {
                books = new Book();
                // 通过字段检索
                books.setId(rs.getInt(1));
                books.setName(rs.getString(2));
                books.setAuthor(rs.getString(3));
                books.setIsbn(rs.getString(4));
                books.setPublish(rs.getString(5));
                books.setBookresume(rs.getString(6));
                books.setPublishdate(rs.getString(7));
                //这里没有把书籍的内容封装进去,为了节省空间
                list.add(books);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBUtils.close(rs,pstmt,conn);
        }
        return list;
    }

    public int deleteUserBook(String id) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        int i = 0;
        try {
            conn = DBUtils.getConn();
            // 创建语句对象
            String sql = "delete from userbooks where id=?";
            pstmt = conn.prepareStatement(sql);
            // 设置参数值
            pstmt.setInt(1, Integer.parseInt(id));
            // 执行删除数据
            i = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.close(null,pstmt,conn);
        }
//        System.out.println("i dao = " + i);
        return i;

    }


}
